package earogya.pharmacy.request_for_drugs;

import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
 
import earogya.pharmacy.request_for_drugs.Request_For_Drugs;
import earogya.util.RequestForDrugsHibernateUtil; 

public class RequestForDrugsHibernateManager {
	/**
	 * Save an object to database using Hibernate
	 * @param obj
	 * @return
	 */
	
	public RequestForDrugsHibernateManager(){
	}
	
	public int saveObject(Object obj) throws HibernateException{
		Integer id=0;
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		try{
			session.beginTransaction();
			id=(Integer)session.save(obj);
			session.getTransaction().commit();
		}catch(HibernateException e){
			throw e;
		}
		return id;
	}
	
	/**
	 * Query results from database based on the fields and values
	 * @param fieldsAndValues - names of the columns and their respective values
	 * @param required_field - name of the required column
	 * @param table - name of the table
	 * @return
	 */
	public int queryValuesFromDatabase(Object[][] fieldsAndValues, String required_field, String table){
		int value=-1;
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();

		session.beginTransaction();
			SQLQuery query=session.createSQLQuery(this.buildSelectQuery(fieldsAndValues, required_field, table));
	        query.setMaxResults(1);
	        List list=query.list();
	        if(list.size()==1){
	        	value=(Integer)list.get(0);
	        }
		return value;
	}
	
	public List queryAllFieldsFromDatabase(Object[][] fieldsAndValues,String table){
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		List list=null;
		session.beginTransaction();
			Query query=session.createQuery(this.buildSelectAllQuery(fieldsAndValues, table));
			list=query.list();
		
		return list;
	}
	
	public List queryValueListFromDatabase(Object[][] fieldsAndValues, String required_field, String table){
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		List list=null;
		session.beginTransaction();
			SQLQuery query=session.createSQLQuery(this.buildSelectQuery(fieldsAndValues, required_field, table));
	        list=query.list();
		
		return list;
	}
	
	public List queryValueListFromDatabase(Object[][] fieldsAndValues, String[] required_field, String className, String objectName){
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		List list=null;
		//try{
			session.beginTransaction();
			Query query=session.createQuery(this.buildSelectQuery(fieldsAndValues, required_field, className, objectName));
	        list=query.list();
		return list;
	}
	
	/**
	 * Creates an sql query using the given information.
	 * @param fieldsAndValues - name of the columns and their respective values
	 * @param required_field - name of the required column 
	 * @param table - name of the table
	 * @return
	 */
	private String buildSelectQuery(Object[][] fieldsAndValues, String[] required_field, String className, String objectName){
		StringBuilder builder=new StringBuilder();
		builder.append("SELECT ");
		for(int i=0; i<required_field.length; i++){
			if(i<required_field.length-1){
				builder.append(objectName+"."+required_field[i]+", ");
			}
			else{
				builder.append(objectName+"."+required_field[i]+" FROM "+className+" AS "+objectName+" WHERE ");
			}
		}
		for(int i=0; i<fieldsAndValues.length; i++){
			if(i<fieldsAndValues.length-1){
				builder.append(objectName+"."+fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"' AND ");
			}else{
				builder.append(objectName+"."+fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"'");
			}
		}
		return builder.toString();
	}
	

	/**
	 * Creates an sql query using the given information.
	 * @param fieldsAndValues - name of the columns and their respective values
	 * @param required_field - names of the required column 
	 * @param table - name of the table
	 * @return
	 */
	private String buildSelectQuery(Object[][] fieldsAndValues, String required_field, String table){
		StringBuilder builder=new StringBuilder();
		builder.append("SELECT "+required_field+" FROM "+table+" WHERE ");
		for(int i=0; i<fieldsAndValues.length; i++){
			if(i<fieldsAndValues.length-1){
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"' AND ");
			}else{
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"'");
			}
		}
		return builder.toString();
	}
	
	private String buildSelectAllQuery(Object[][] fieldsAndValues, String table){
		StringBuilder builder=new StringBuilder();
		builder.append("SELECT {patient.*} FROM "+table+" WHERE ");
		for(int i=0; i<fieldsAndValues.length; i++){
			if(i<fieldsAndValues.length-1){
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"' AND ");
			}else{
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"'");
			}
		}
		return builder.toString();
	}	

	
	private String buildDeleteQuery(Object[][] fieldsAndValues, String table){
		StringBuilder builder=new StringBuilder();
		builder.append("DELETE FROM "+table+" WHERE ");
		for(int i=0; i<fieldsAndValues.length; i++){
			if(i<fieldsAndValues.length-1){
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"' AND ");
			}else{
				builder.append(fieldsAndValues[i][0]+"='"+fieldsAndValues[i][1]+"'");
			}
		}
		return builder.toString();
	}
	
	public Boolean deleteEntry(Object[][] fieldsAndValues, String table){
		Boolean isSuccesful=false;
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		try{
			session.beginTransaction();
			SQLQuery query=session.createSQLQuery(this.buildDeleteQuery(fieldsAndValues, table));
			int no_of_rows=query.executeUpdate();
			if(no_of_rows>0){
				isSuccesful=true;
			}
		}catch(HibernateException e){
			isSuccesful=false;
		}finally{
			session.close();
		}
		return isSuccesful;
	}
	
	public Boolean update(Request_For_Drugs requestDrugs){
		Boolean isSuccessful=false;
		Session session = RequestForDrugsHibernateUtil.getSessionFactory().getCurrentSession();
		try{
			session.beginTransaction();
			Request_For_Drugs requestDrugs1=(Request_For_Drugs)session.get(Request_For_Drugs.class,requestDrugs.getRequestId());
			if(requestDrugs.getDrugId()== 0){}else{requestDrugs1.setDrugId(requestDrugs.getDrugId());}
			if(requestDrugs.getQuantity()== 0){}else{requestDrugs1.setQuantity(requestDrugs.getQuantity());}
			if(requestDrugs.getEnteredByID()==0){}else{requestDrugs1.setEnteredByID(requestDrugs.getEnteredByID());}
			if(requestDrugs.getVerifiedByID()==0){}else{requestDrugs1.setVerifiedByID(requestDrugs.getVerifiedByID());}
			if(requestDrugs.getMinAmount()==0){}else{requestDrugs1.setMinAmount(requestDrugs.getMinAmount());}
			if(requestDrugs.getMaxAmount()==0){}else{requestDrugs1.setMaxAmount(requestDrugs.getMaxAmount());}
			if(requestDrugs.getAmountUnits()==null){}else{requestDrugs1.setAmountUnits(requestDrugs.getAmountUnits());}
			if(requestDrugs.getUrgency()==null){}else{requestDrugs1.setUrgency(requestDrugs.getUrgency());}
			if(requestDrugs.getUnitOrWard()==null){}else{requestDrugs1.setUnitOrWard(requestDrugs.getUnitOrWard());}
			if(requestDrugs.getRequestDate()==null){}else{requestDrugs1.setRequestDate(requestDrugs.getRequestDate());}
			if(requestDrugs.getRequestTime()==null){}else{requestDrugs1.setRequestTime(requestDrugs.getRequestTime());}
			session.update(requestDrugs1);
			session.getTransaction().commit();
			isSuccessful=true;
		}catch(HibernateException e){
			isSuccessful=false;
			throw e;
		}
		return isSuccessful;

	}
	
}

